﻿
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;

using Excel = Microsoft.Office.Interop.Excel;

using Finisar.SQLite;
using System.Data.Sql;


namespace lp1415_6316_11112
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;

            string str;
            double ? num;
            int rCnt = 0;
            int cCnt = 0;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\Ricardo\\Documents\\Disciplinas\\14-15\\1º Semestre\\Linguagens de Programaçao\\Projecto C#\\lp1415-6316-11112\\lp1415-6316-11112\\IPC_Portugal_1977_2013.xls");
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;

          
                for (cCnt = 2; cCnt <= range.Columns.Count; cCnt++)
                {
                    str = (range.Cells[1, cCnt] as Excel.Range).Value;

                    
                    if (str == null)
                    {
                        break;
                    }
                    
                    else
                    {
                        MessageBox.Show(str);   
                    }
                }
           

            for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 2; cCnt <= range.Columns.Count; cCnt++)
                {
                    num = (range.Cells[rCnt, cCnt] as Excel.Range).Value;

                        MessageBox.Show(num.ToString());
                }
            }

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        public void importdatafromexcel(string excelfilepath)
{
    //declare variables - edit these based on your particular situation
    string ssqltable = "tdatamigrationtable";
    // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
    string myexceldataquery = "select student,rollno,course from [sheet1$]";
    try
    {
        //create our connection strings
        string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath +
        ";extended properties=" + "\"excel 8.0;hdr=yes;\"";
        string ssqlconnectionstring = "server=mydatabaseservername;userid=dbuserid;password=dbuserpassword;database=databasename;connection reset=false";
        //execute a query to erase any previous data from our destination table
        string sclearsql = "delete from " + ssqltable;
        sqlconnection sqlconn = new sqlconnection(ssqlconnectionstring);
        sqlcommand sqlcmd = new sqlcommand(sclearsql, sqlconn);
        sqlconn.open();
        sqlcmd.executenonquery();
        sqlconn.close();
        //series of commands to bulk copy data from the excel file into our sql table
        oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring);
        oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn);
        oledbconn.open();
        oledbdatareader dr = oledbcmd.executereader();
        sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring);
        bulkcopy.destinationtablename = ssqltable;
        while (dr.read())
        {
            bulkcopy.writetoserver(dr);
        }
     
        oledbconn.close();
    }
    catch (exception ex)
    {
        //handle exception
    }
}

    }
}

